package org.odk.collect.android.cse481k;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;


import org.odk.collect.android.database.ODKSQLiteOpenHelper;
import org.odk.collect.android.utilities.FileUtils;


/**
 * Generates and handles all DB operations
 * @author Chiyoung
 *
 */
public class NamesDbController {
    private static final String DATABASE_CREATE = "create table names (name text primary key, lat real not null, " +
    								"lon real not null);";
    private static final String DATABASE_NAME = "formInfo";
    private static final String DATABASE_TABLE = "names";
    private static final String DATABASE_PATH = FileUtils.ODK_ROOT + "metadata/";
    private static final int DATABASE_VERSION = 7;
    private static final String selectAll = "SELECT * FROM names;";
    
    private static double lat;
    private static double lon;
    
    private static final double RANGE = 3.0;
    
    /**
     * 
     * @author Chiyoung
     *
     */
	private static class NameDbHelper extends ODKSQLiteOpenHelper{

		public NameDbHelper() {
			super(DATABASE_PATH, DATABASE_NAME, null, DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}
		
        @Override
        public void onCreate(SQLiteDatabase db){
            db.execSQL(DATABASE_CREATE);
            /*
             * UW's GPS coordinates:
             * Latitude: 47.6536
             * Longitude: -122.30605
             * 
             */
           
    		ContentValues val = new ContentValues();
    		val.put("name", "monkey");
    		val.put("lat", 47.6536);
    		val.put("lon", -122.30605);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		val.put("name", "fish");
    		val.put("lat", 48.00);
    		val.put("lon", -121.0);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		val.put("name", "bird");
    		val.put("lat", 46.0);
    		val.put("lon", -120.0);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		val.put("name", "zedi");
    		val.put("lat", 49.0);
    		val.put("lon", -110.0);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		val.put("name", "ant");
    		val.put("lat", 60.0);
    		val.put("lon", -124.0);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		val.put("name", "zombi");
    		val.put("lat", 50.0);
    		val.put("lon", -118.0);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		val.put("name", "lion");
    		val.put("lat", 42.0);
    		val.put("lon", -50.0);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		val.put("name", "bad_lion");
    		val.put("lat", 40.0);
    		val.put("lon", -122.0);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		val.put("name", "good_lion");
    		val.put("lat", 47.0);
    		val.put("lon", -140.0);
    		db.insert(DATABASE_TABLE, null, val);
    		val.clear();
    		           
        }

        @Override
        // upgrading will destroy all old data
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
            onCreate(db);
        }		
	}
	
	private NameDbHelper nDh;
    private SQLiteDatabase nDb;
    
    
    /**
     * 
     * @param lat Latitude
     * @param lon Longitude
     */
    public NamesDbController(double lat, double lon){
    	NamesDbController.lat = lat;
    	NamesDbController.lon = lon;
    }
    
    /**
     * Generate and populate the db
     */
    public synchronized void populate(){
        nDh = new NameDbHelper();
        nDb = nDh.getWritableDatabase();
    }
    
    /**
     * Close DB
     */
    public synchronized void close(){
    	nDh.close();
    	nDb.close();
    }
    
    /**
     * Fetches the list of names that are within RANGE(default: +-10.0) from current position
     * @return Cursor object containing the results from the DB
     */
    public synchronized Cursor fetchNames(){
    	Cursor c = null;
    	String query = "SELECT name FROM names WHERE lat >= " + (lat-RANGE) + " AND lat <= " + (lat+RANGE) +
    					" AND lon >= " + (lon-RANGE) + " AND lon <= " + (lon+RANGE);
    	
    	c = nDb.rawQuery(query, null);    	
    	
    	if(c != null){
    		int count = c.getCount();
    		//c.moveToFirst();
    	} else
    		Log.i("dbEmpty", "DB went wrong");
    	return c;
    }
    
    

}
